package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import domain.*;

public class AdministradorPersistenciaPlan {

	private static AdministradorPersistenciaPlan pool;

	private AdministradorPersistenciaPlan() {

	}

	public static AdministradorPersistenciaPlan getInstancia() {
		if (pool == null)
			pool = new AdministradorPersistenciaPlan();
		return pool;
	}

	public Plan getPlan(int planId) {
		Vector<Plan> v = new Vector<Plan>();
		Connection con = PoolConnection.getPoolConnection().getConnection();
		try {
			String senten = "SELECT * from Planes where PlanId = ?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setInt(1, planId);
			ResultSet result = ps.executeQuery();

			fillPlanes(result, v, con);

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Borrar Cliente: "
					+ e.getMessage());
			System.out.println("Stack Trace al Borrar Cliente: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}

		return v.firstElement();
	}
	
	public void insertarPaquete(Paquete paquete) {
		Connection con = PoolConnection.getPoolConnection().getConnection();
		try {
			String senten = "INSERT INTO Planes (Facturable ,MinutosLibres,CostoAdicional,TipoPlan,CostoAdicionalPorVoz,Nombre) VALUES(NULL,NULL,NULL,'Paquete',NULL,?)";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten, Statement.RETURN_GENERATED_KEYS);
			ps.setString(1, paquete.getNombre());
			ps.executeUpdate();
			ResultSet result = ps.getGeneratedKeys();
			
			if (result.next()) {
				for (Plan p : paquete.getChildPlanes()){
					String s = "INSERT INTO PlanesPaquete (PlanId,PaquetePlanId) VALUES(?,?)";
					ps = con.prepareStatement(s);
					ps.setInt(1, result.getInt(1));
					ps.setInt(2, p.getId());
					ps.execute();
				}	            
	        } else {
	            throw new SQLException("Creating user failed, no generated key obtained.");
	        }		
			

			PoolConnection.getPoolConnection().realeaseConnection(con);
			
		} catch (SQLException e) {
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
	}
	
	public int getAdicional() {
		Connection con = PoolConnection.getPoolConnection().getConnection();
		try {
			String senten = "SELECT * from Planes where TipoPlan = ?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, "Adicional");
			ResultSet result = ps.executeQuery();

			PoolConnection.getPoolConnection().realeaseConnection(con);
			return result.getInt("PlanId");
		} catch (SQLException e) {
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return -1;
	}

	public Vector<Plan> obtenerTodos() {
		Vector<Plan> v = new Vector<Plan>();
		Connection con = PoolConnection.getPoolConnection().getConnection();
		try {

			// Preparar sentencia insert en tabla destino
			String senten = "SELECT * from Planes";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ResultSet result = ps.executeQuery();

			fillPlanes(result, v, con);

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Borrar Cliente: "
					+ e.getMessage());
			System.out.println("Stack Trace al Borrar Cliente: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return v;
	}

	private void fillPaqueteChilds(Paquete paquete, Connection con)
			throws SQLException {
		String senten = "  select p.PlanId,p.Facturable,MinutosLibres,CostoAdicional,TipoPlan ,CostoAdicionalPorVoz from Planes p join PlanesPaquete on p.PlanId = PlanesPaquete.PaquetePlanId where PlanesPaquete.PlanId = ?";
		PreparedStatement ps = null;
		ps = con.prepareStatement(senten);
		ps.setInt(1, paquete.getId());
		ResultSet result = ps.executeQuery();
		Vector<Plan> v = new Vector<Plan>();

		fillPlanes(result, v, con);

		for (Plan p : v) {
			paquete.agregarPlan(p);
		}
	}

	private void fillPlanes(ResultSet result, Vector<Plan> v, Connection con)
			throws SQLException {

		while (result.next()) {
			Plan p = null;
			if (result.getString("TipoPlan").equals("Prepago")) {
				p = new Prepago(result.getBoolean("Facturable"),
						result.getInt("PlanId"));
			} else if (result.getString("TipoPlan").equals("Adicional")) {
				p = new Adicional(result.getBoolean("Facturable"),
						result.getInt("PlanId"),
						result.getInt("MinutosLibres"),
						result.getFloat("CostoAdicional")); 
						// result.getString("Nombre")); // TODO el nombre hay q agregarlo a la tabla linea no? 
													// (no existe en plan)
			} else if (result.getString("TipoPlan").equals("Base")) {
				p = new Base(result.getBoolean("Facturable"),
						result.getInt("PlanId"),
						result.getInt("MinutosLibres"),
						result.getFloat("CostoAdicional"));
			}

			else if (result.getString("TipoPlan").equals("Paquete")) {
				p = new Paquete(result.getInt("PlanId"), result.getString("Nombre"));
				fillPaqueteChilds((Paquete) p, con);

			}
			v.add(p);
		}
	}
}
